package com.pagepub.expo.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;
import com.pagepub.expo.entity.AppDict;
import com.pagepub.expo.entity.IdName;
import com.pagepub.expo.dao.AppSeqDao;

@Repository
public class AppDictDao {
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate template;
	
	@Autowired
	@Qualifier("appSeqDao")
	private AppSeqDao appSeqDao;

	private static String QUERY_ALL = "SELECT id,dictName,dictCode,classCode,classValue,subdictCode FROM App_Dict";

	private static String ID_NAME="SELECT classcode id, classValue name FROM App_Dict";
	
	private static String QUERY_ALL_COUNT = "SELECT COUNT(*) FROM App_Dict";
	
	private static String INSERT_SQL = "INSERT  INTO App_Dict(id,dictName,dictCode,classCode,classValue,subdictCode)"
			+ " VALUES(:id,:dictName,:dictCode,:classCode,:classValue,:subdictCode)";

	private static String DELETE_SQL = "DELETE FROM App_Dict WHERE id=?";

	private static String UPDATE_SQL = "UPDATE App_Dict SET dictName=:dictName, dictCode=:dictCode,"
			+ "classCode=:classCode, classValue=:classValue,subdictCode=:subdictCode WHERE id=:id";

	private final ParameterizedRowMapper<AppDict> appDictMapper = new ParameterizedRowMapper<AppDict>() {
		public AppDict mapRow(ResultSet rs, int rowNum) throws SQLException {
			AppDict ad = new AppDict();
			ad.setId(rs.getLong("id"));
			ad.setDictName(rs.getString("dictname"));
			ad.setDictCode(rs.getString("dictcode"));
			ad.setClassValue(rs.getString("classvalue"));
			ad.setClassCode(rs.getString("classcode"));
			ad.setSubdictCode(rs.getString("subdictcode"));
			return ad;
		}
	};

	public List<AppDict> findAll() {
		return template.query(QUERY_ALL, appDictMapper);
	}

	public AppDict findById(Long id) {
		List<AppDict> appDicts = template.query(QUERY_ALL + " WHERE id=?",
				new Object[] { id }, appDictMapper);
		if (appDicts.size() == 0) {
			return null;
		} else
			return appDicts.get(0);
	}
	

	
	public void insert(AppDict appDict) {
		if(appDict.getId()==null){
			appDict.setId(new Long(appSeqDao.getSequence("app_dict")));
		}		
		SqlParameterSource ps = new BeanPropertySqlParameterSource(appDict);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(INSERT_SQL, ps);

	}

	public void delete(Long id) {
		template.update(DELETE_SQL, id);
	}

	public void update(AppDict AppDict) {
		SqlParameterSource ps = new BeanPropertySqlParameterSource(AppDict);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(UPDATE_SQL, ps);
	}
	
	public int getCount(Map<String, String> param){
		int count = template.queryForInt(QUERY_ALL_COUNT);
		return count;
	}
	
	public List<AppDict> findByPage(int currentPage,int pageSize,Map<String, String> param){
		List<AppDict> appDicts = template.query(QUERY_ALL + " limit ?,?",
				new Object[] { currentPage, pageSize}, appDictMapper);
		return appDicts;
		
	}
	
	public List<AppDict> getfindByDictCode(String  dictCode){
		List<AppDict> appDicts = template.query(QUERY_ALL + " WHERE subdictCode=?",
				new Object[] { dictCode }, appDictMapper);
		return appDicts;
		
	}
	
	public List<IdName> findIdNames(String dictCode){
		List lists = template.query(ID_NAME + " where subdictCode=?", new Object[] { dictCode },
				IdName.idNameMapper);
		return lists;
	}
}

